package com.cnooc.zyfx;


import com.cnooc.zyfx.entity.Entities;
import com.cnooc.zyfx.entity.Entities.queryKPI;
import com.github.thorqin.toolkit.Application;
import com.github.thorqin.toolkit.annotation.Service;
import com.github.thorqin.toolkit.db.DBService;
import com.github.thorqin.toolkit.db.DBService.DBTable;
import com.github.thorqin.toolkit.validation.annotation.Validate;
import com.github.thorqin.toolkit.web.HttpException;
import com.github.thorqin.toolkit.web.annotation.*;
import com.github.thorqin.toolkit.web.router.WebContent;
import com.github.thorqin.toolkit.web.session.WebSession;
import com.github.thorqin.toolkit.utility.Serializer;

import java.io.IOException;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.joda.time.DateTime;

@WebModule(value="/api")
public class AnalysisModule {

   // @Service("db")
   // DBService db;
	
	@Service("application")
    Application app;
	
    @Service("sqlServer")
    DBService msdb;
    
    public static class Obj {
		public String key;
		public String value;
	}
    
    	 
    private String makeSql(Entities.queryAnalysisInput input, List<Object> args) {
    	
    	String sql = "select #FIELD# from  v_Query_analysis v where (\n" +
			"(OrderNumber=2 and abs(KeyValue) < 1.05*abs(BenchmarkValue))\n" +
			"or ( OrderNumber=1 and KeyValue < BenchmarkValue)\n" +
			"or (OrderNumber=0	and KeyValue > BenchmarkValue)\n" +
			") ";
    	
    	if (input.sectionId != null) {
    		sql += " and BuzBoardCode=?";
    		args.add(input.sectionId);
    	}else{
    		sql += " and BuzBoardCode in (?)";
    		args.add(input.sections);
    	}
    	if (input.KPIs != null && input.KPIs.size() > 0) {
    		sql += " and FieldCode in (?)";
    		args.add(input.KPIs);
    	}
    	if (input.companyId != null && input.companyId.size() > 0) {
    		sql += " and CompCode in (?)";
    		args.add(input.companyId);
    	}
    	//SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	//String startTime = formatter.format(input.startTime);
    	
    	if (input.startTime != null && input.endTime != null) {
    		sql += " and ReportDate between ? and ?";
    		args.add(input.startTime);
    		args.add(input.endTime);
    	}
    	if (input.startTime != null && input.endTime == null) {
    		sql += " and ReportDate >= ?";
    		args.add(input.startTime);
    	}
    	if (input.startTime == null && input.endTime != null) {
    		sql += " and ReportDate <= ?";
    		args.add(input.endTime);
    	}
  
//    	默认显示当前年
    	if (input.startTime == null && input.endTime == null) {
    		sql += " and ReportDate > DATENAME(YEAR,GETDATE())  ";
    	}
    	return sql;
    }
    
    /*comments functions*/
    @WebEntry(method = HttpMethod.POST)
    public DBTable queryAnalysis(@Entity Entities.queryAnalysisInput input) throws SQLException, IOException {
        try (DBService.DBSession session = msdb.getSession(false)) {
        	String pageBegin = "select top 10 * from (";
        	String pageEnd = ") tt where num > ?";
        	String fieldList = "0 checked, ROW_NUMBER() over(order by ReportDate desc) as num,"
        			+ "* ";
        	List<Object> args = new LinkedList<>();
        	String sql = makeSql(input, args);
        	String querySql = pageBegin + sql.replace("#FIELD#", fieldList) + pageEnd;
        	String countSql = sql.replace("#FIELD#", "count(0) as ct");
        	
        	int len = (Integer)session.queryFirst(countSql, args.toArray()).get("ct");
        	args.add(10 * (input.page-1));
            DBTable tb = session.queryTable(querySql,args.toArray());
            tb.length = len;
            return tb;
        }
    }
    
    @WebEntry(method = HttpMethod.POST)
    public void addAnalysis(@Entity  Entities.addAnalysisInput input,WebSession websession) throws IOException, SQLException {
    	try (DBService.DBSession session = msdb.getSession()) { 
	    	try {
	    		String UserName=(String)websession.get("name");
				session.execute(app.readScript("sql/addAnalysis.sql"), input.date, input.date.getMonth()+1, input.KPI,
						input.analysis,input.improve,input.companyId,
						new Date(),(String)websession.get("uid"),UserName,new Date(),UserName);
			} catch (SQLException e) {
				if (e.getMessage().startsWith("String or binary data would be truncated")) {
		    		throw new HttpException(400,"字数超过限制");
		    	}
			}
	    }
	}
    
    @WebEntry(method = HttpMethod.POST)
    public void deleteAnalysis(@Entity Entities.deleteInput input) throws SQLException {
        try (DBService.DBSession session = msdb.getSession()) {
            session.execute("delete from ProImproved where id in (?)",input.id);
        }
    }
    
    @WebEntry(method = HttpMethod.POST)
    public void modifyAnalysis(@Entity Entities.modifyAnalysisInput input,WebSession websession) throws SQLException, IOException {
	    try (DBService.DBSession session = msdb.getSession()) { 
	    	session.execute(app.readScript("sql/modifyAnalysis.sql"),input.analysis , input.improve,
	    			new Date(),(String)websession.get("uid"),(String)websession.get("name"),input.id );
	    }catch (SQLException e) {
			if (e.getMessage().startsWith("String or binary data would be truncated")) {
	    		throw new HttpException(400,"字数超过限制");
	    	}
		}
	}
    
}

